package by.epam.communalservices.dao.jdbc;

import java.sql.*;
import java.util.*;

import by.epam.communalservices.dao.DaoJkhWorker;
import by.epam.communalservices.entity.JkhWorker;
import by.epam.communalservices.exception.JkhDaoException;
import org.apache.log4j.Logger;

public class JdbcDaoJkhWorker implements DaoJkhWorker {

    private Connection con;

    private final String COLUMN_JKH_WORKER_ID = "jkh_worker_id";

    private final String COLUMN_SURNAME = "surname";

    private final String COLUMN_NAME = "name";

    private final String COLUMN_PATRONYMIC = "patronymic";

    private final String COLUMN_SPECIALTY = "specialty";

    private final String COLUMN_RATE = "rate";
    
    static Logger JdbcDaoJkhWorkerLoger = Logger.getLogger(JdbcDaoJkhWorker.class);

    public JdbcDaoJkhWorker(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException(
                        "Connection error. Connection is not open",
                        new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            JdbcDaoJkhWorkerLoger.error("Connection error",sx);
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error",
                    new Exception());

        }
    }

    public Long create(JkhWorker jkhWorker) {
        Long genid = null;
        String sql = "INSERT INTO public.jkh_worker(name,surname," +
        		"patronymic,specialty,rate) VALUES (?,?,?,?,?)";
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql,
                        Statement.RETURN_GENERATED_KEYS);
                pstate.setString(1, jkhWorker.getSurname());
                pstate.setString(2, jkhWorker.getName());
                pstate.setString(3, jkhWorker.getPatronymic());
                pstate.setString(4, jkhWorker.getSpecialty());
                pstate.setInt(5, jkhWorker.getRate());
                pstate.execute();
                result = pstate.getGeneratedKeys();
                if (result.next()) {
                    genid = result.getLong(1);
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                //System.out.println("query execute error");
                JdbcDaoJkhWorkerLoger.error("Query execute error",ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    //System.out.print("Error close result");
                    JdbcDaoJkhWorkerLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    //System.out.print("Error close statement");
                    JdbcDaoJkhWorkerLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return genid;
    }

    public JkhWorker read(Long id) {
        PreparedStatement pstate = null;
        ResultSet result = null;
        JkhWorker worker = null;
        String sql = "SELECT * FROM public.jkh_worker where jkh_worker_id=?";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                //System.out.println("query execute error");
                JdbcDaoJkhWorkerLoger.error("Query execute error",ex);
                ex.printStackTrace();
            }
            try {
                if (result.next()) {
                    worker = new JkhWorker(
                            result.getLong(COLUMN_JKH_WORKER_ID), 
                            result.getString(COLUMN_NAME), 
                            result.getString(COLUMN_SURNAME), 
                            result.getString(COLUMN_PATRONYMIC), 
                            result.getString(COLUMN_SPECIALTY), 
                            result.getInt(COLUMN_RATE));
                } else
                    throw new SQLException();
            } catch (SQLException ex) {
                //System.out.println("record not found");
                JdbcDaoJkhWorkerLoger.error("Record not found",ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    //System.out.print("Error close result");
                    JdbcDaoJkhWorkerLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    //System.out.print("Error close statement");
                    JdbcDaoJkhWorkerLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return worker;
    }

    public void update(Long id, JkhWorker jkhWorker) {
        PreparedStatement pstate = null;
        String sql = "UPDATE public.jkh_worker SET surname=?, "
                + "name=?, patronymic=?, specialty=?, rate=? "
                + "where jkh_worker_id=?";
        try {
            pstate = con.prepareStatement(sql);
            pstate.setString(1, jkhWorker.getSurname());
            pstate.setString(2, jkhWorker.getName());
            pstate.setString(3, jkhWorker.getPatronymic());
            pstate.setString(4, jkhWorker.getSpecialty());
            pstate.setInt(5, jkhWorker.getRate());
            pstate.setLong(6, id);
            pstate.execute();

        } catch (SQLException ex) {
            //System.out.println("query execute error");
            JdbcDaoJkhWorkerLoger.error("Query execute error",ex);
            ex.printStackTrace();
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    //System.out.print("Error close statement");
                    JdbcDaoJkhWorkerLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
    }

    public void delete(Long id) {
        PreparedStatement pstate = null;
        String sql = "DELETE FROM public.jkh_worker where jkh_worker_id=?";
        try {
            pstate = con.prepareStatement(sql);
            pstate.setLong(1, id);
            pstate.execute();
        } catch (SQLException ex) {
            //System.out.println("query execute error");
            JdbcDaoJkhWorkerLoger.error("Query execute error",ex);
            ex.printStackTrace();
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    //System.out.print("Error close statement");
                    JdbcDaoJkhWorkerLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
    }

    public List<JkhWorker> findAll() {
        @SuppressWarnings("unchecked")
        List<JkhWorker> list = Collections.EMPTY_LIST;
        Statement pstate = null;
        ResultSet result = null;
        String sql = "SELECT * from public.jkh_worker ORDER BY jkh_worker_id";
        try {
            pstate = con.createStatement();
            result = pstate.executeQuery(sql);
            list = new ArrayList<JkhWorker>();
            while (result.next()) {
                JkhWorker jkhWorker = new JkhWorker(
                        result.getLong(COLUMN_JKH_WORKER_ID), 
                        result.getString(COLUMN_NAME), 
                        result.getString(COLUMN_SURNAME), 
                        result.getString(COLUMN_PATRONYMIC), 
                        result.getString(COLUMN_SPECIALTY), 
                        result.getInt(COLUMN_RATE));
                list.add(jkhWorker);
            }
        } catch (SQLException ex) {
            JdbcDaoJkhWorkerLoger.error("Error creating the list",ex);
            ex.printStackTrace();
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoJkhWorkerLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoJkhWorkerLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return list;
    }
}
